#!/usr/bin/env python
# -*- coding: utf-8 -*-
#
#      This file is distributed as part of the WebAMP2 project and is licensed
#      under the GPL version 3.
#
#      Copyright 2007 Chris Seickel <cseickel@gmail.com>
#
#      This program is free software; you can redistribute it and/or modify
#      it under the terms of the GNU General Public License version 3 as 
#      published by the Free Software Foundation.
#
#      This program is distributed in the hope that it will be useful,
#      but WITHOUT ANY WARRANTY; without even the implied warranty of
#      MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#      GNU General Public License version 3 for more details.
#
#      You should have received a copy of the GNU General Public License
#      along with this program; if not, write to:
#       Free Software Foundation, Inc.
#       51 Franklin Street, Fifth Floor
#       Boston, MA 02110-1301, USA


import os, glob, codecs, cgi, difflib, datetime, time, urllib
from sqlite3 import dbapi2

BASE_URL = "http://localhost:8080"

table_defs = """CREATE TABLE IF NOT EXISTS stats
(
  artists       TEXT,
  albums        TEXT, 
  songs         TEXT,
  db_playtime   TEXT,
  db_update     TEXT
);

CREATE TABLE IF NOT EXISTS songs
(
  song_id   INTEGER UNIQUE PRIMARY KEY AUTOINCREMENT,
  file      TEXT UNIQUE,
  name      TEXT,
  dir_id    INTEGER,
  title     TEXT,
  album_id  INTEGER,
  artist_id INTEGER,
  track     TEXT,
  time      TEXT,
  updated   INTEGER,
  playcount INTEGER,
  lastplay  INTEGER,
  lyrics    TEXT
);

CREATE TABLE IF NOT EXISTS dirs
(
  dir_id    INTEGER UNIQUE PRIMARY KEY AUTOINCREMENT,
  name      TEXT,
  path      INTEGER
);

CREATE TABLE IF NOT EXISTS artists
(
  artist_id INTEGER UNIQUE PRIMARY KEY AUTOINCREMENT,
  name      TEXT,
  mb_id     TEXT
);

CREATE TABLE IF NOT EXISTS albums
(
  album_id  INTEGER UNIQUE PRIMARY KEY AUTOINCREMENT,
  name      TEXT,
  mb_id     TEXT,
  asin      TEXT
);
"""

RDF_header = """<?xml version="1.0"?>
<RDF:RDF xmlns:RDF="http://www.w3.org/1999/02/22-rdf-syntax-ns#"
         xmlns:s="http://webamp2/RDF#"
         xmlns:nc="http://home.netscape.com/NC-rdf#">

"""
con = dbapi2.connect("webamp2.sqlite")
con.executescript(table_defs)
con.commit()
cur = con.cursor()

#If this is a new database, insert the root record into dirs table
cur.execute("SELECT * FROM dirs")
root = cur.fetchone()
if root == None:
    cur.execute("INSERT INTO dirs VALUES (0,'',-1)")
    con.commit()
con.close()

def getCursor():
    conn = dbapi2.connect("webamp2.sqlite")
    cursor = conn.cursor()
    return conn, cursor

def esc(s): return cgi.escape(s) #.replace('"','&#34;').replace("'",'&#39;')

def fractSec(s):
    "Return days, hours, minutes, seconds given a value in seconds"
    m, s = divmod(s, 60)
    h, m = divmod(m, 60)
    d, h = divmod(h, 24)
    return d, h, m, s

def minutes(val):
    "Returns hh:mm:ss formatted time from seconds"
    hr = ""
    mn = "0:"
    sec = "00" 
    try:
        val = int(val)
        d, h, m, s = fractSec(val)
        if (h > 0):
                hr = str(h)+":"
        else:
                hr = ""
        mn = str(m)+":"
        if (s < 10):
                sec = "0"+str(s)
        else:
                sec = str(s)
    except: pass
    return hr+mn+sec

def guessTags(song):
    #try:
    if not song.has_key("file"):
        return song
    
    artist = _artist = ""
    album = _album = ""
    title = _title = ""
    track = _track = ""
    if (song.has_key("album")):
        album = song["album"]
    if (song.has_key("artist")):
        artist = song["artist"]
    if (song.has_key("title")):
        title = song["title"]
    if (song.has_key("track")):
        track = song["track"]
        
    if (title=="" or album=="" or artist=="" or track==""):
        myfile = song["file"].split("/").pop()[:-4]
        _title = myfile
        s = myfile.find("(")
        if (s >= 0):
            e = myfile.rfind(")")
            if (e > s):
                if s > 0: s -+ 1
                if e < (len(myfile)-1): e += 1
                myfile = myfile[:s] + myfile[e:]
        s = myfile.split("-")
        s = [x.strip() for x in s]
        
        t = []
        for x in s:
            if x.isdigit():
                t.append(int(x))
                s.remove(x)
        if len(t) > 0:
            t.sort()
            _track = t[0]
            
        if (len(s) > 0):
            _title = s[-1]
            if len(s) > 1:
                _artist = s[0]
            if len(s) > 2:
                _album = u' '.join(s[1:-1])
        if (track == ""): track = str(_track)
        if (title == ""): title = _title
        if (artist == ""): artist = _artist
        if (album == ""): album = _album
        song["album"] = album
        song["artist"] = artist
        song["title"] = title
        song["track"] = track
    #except:
        #print "Error parsing file name"
    return song


def addSong(song, con, cur, commit=True, update=True):
    """Adds a song to the database and returns the id.
    Expects a dictionary with the following keys:
        file = String, filename with complete path, 
        title = String,
        album = String,
        artist = String,
        time = String or Integer song length in seconds,
        track = String,
        updated = Integer, Unix style datetime."""
    
    name = song['file'].split('/')[-1]
    path = song['file'][:-(len("/"+name))]
    needadd = needupdate = False
    #Check if this song exists
    cur.execute("SELECT song_id, updated FROM songs WHERE file=?", (song["file"],))
    row = cur.fetchone()
    if row <> None: 
        if row[1] > song['updated']:
            needupdate = True
    else:
        needadd = True
    if needadd or needupdate:
        # First guess tags by parsing filename if needed.
        song = guessTags(song)
        #Fetch the dir_id or add the directory if it does not yet exist.
        #If the path does not exist, we need to add all parents.
        cur.execute("SELECT dir_id FROM dirs WHERE name=?", (path,))
        pathrow = cur.fetchone()
        if pathrow == None:
            pieces = path.split("/")
            subdirs = [u'/'.join(pieces[0:-x]) for x in range(1, len(pieces))]
            subdirs.sort()
            for d in subdirs:
                cur.execute("SELECT dir_id FROM dirs WHERE name=?", (d,))
                drow = cur.fetchone()
                if drow == None:
                    sub = u'/'.join(d.split("/")[0:-1])
                    cur.execute("SELECT dir_id FROM dirs WHERE name=?", (sub,))
                    subrow = cur.fetchone()
                    if subrow == None: subid = 1
                    else: subid = subrow[0]
                    cur.execute("INSERT INTO dirs (name, path) VALUES (?, ?)",
                                (d,subid))
            sub = u'/'.join(path.split("/")[0:-1])
            cur.execute("SELECT dir_id FROM dirs WHERE name=?", (sub,))
            subrow = cur.fetchone()
            if subrow == None: subid = 0
            else: subid = subrow[0]
            cur.execute("INSERT INTO dirs (name, path) VALUES (?, ?)",
                        (path,subid))
            pathrow = [cur.lastrowid]
        
        #Fetch the artist id or add the artist if it does not yet exist.
        cur.execute("SELECT artist_id FROM artists WHERE name=?",
                    (song['artist'],))
        artist = cur.fetchone()
        if artist == None:
            cur.execute("INSERT INTO artists (name) VALUES (?)", 
                        (song['artist'],))
            artist = [cur.lastrowid]
         
        #Fetch the album id or add the album if it does not yet exist.
        cur.execute("SELECT album_id FROM albums WHERE name=?",
                    (song['album'],))
        album = cur.fetchone()
        if album == None:
            cur.execute("INSERT INTO albums (name) VALUES (?)", 
                        (song['album'],))
            album = [cur.lastrowid]        
        
        values = (song['file'],name,pathrow[0],song['title'],album[0],
                    artist[0],minutes(song['time']),song['track'],
                    song['updated'], 0, 0)
    if needadd: 
        try: print "Adding: "+song["file"]
        except: print "Adding: ???"
        insert = """INSERT INTO songs 
                    (
                    file, name, dir_id, title, album_id,
                    artist_id, time, track, updated, playcount, lastplay
                    ) 
                    VALUES (?,?,?,?,?,?,?,?,?,?,?)"""
        cur.execute(insert, values)
        return True
    elif needupdate and update:
        try: print "Updating: "+song["file"]
        except: print "Updating: ???"
        update = """UPDATE songs
                    SET file=?, name=?, dir_id=?, title=?, album_id=?,
                        artist_id=?, time=?, track=?, updated=?
                    WHERE song_id="""+str(row[0])
        cur.execute(update, values)
        return True
    else:
        return False
    if commit: con.commit()
    

def group(con, cur, threshold=1):
    print "Grouping artists."
    select = """SELECT artists.name, COUNT(artists.name) FROM songs
                   INNER JOIN artists ON songs.artist_id=artists.artist_id
                   WHERE artists.name > '' 
                   GROUP BY artists.name
                   ORDER BY artists.name"""
    cur.execute(select)
    lst = cur.fetchall()
    alist = [x[0] for x in lst if x[1] > threshold]
    all = set([x[0] for x in lst])
    print str(len(all))," unique artists, ", str(len(alist)), " repeating artists."
    grouped = []
    seq = difflib.SequenceMatcher()
    while alist and all:
        art = alist.pop(0)
        test = art.lower().replace("the ","")
        sim = []
        seq.set_seq2(test)
        ratio = 0.70 + ((24 - float(len(test)))/100.00)
        if ratio < 0.60: ratio = 0.60
        if ratio > 100.00: ratio = 100.00
        for x in all:
            r = 0.00
            xtest = x.lower().replace("the ","")
            if test == xtest: r = 100.00
            elif (len(test) > 4):
                seq.set_seq1(xtest)
                r = seq.quick_ratio()
                if r >= ratio: r = seq.ratio()
                if ratio > r > 0.40:
                    xs = xtest.split(", ")
                    if len(xs) == 2:
                        seq.set_seq1(xs[1]+" "+xs[0])
                        r = seq.quick_ratio()
                        if r >= ratio: r = seq.ratio()
            if r >= ratio:
                sim.append(x)
                    
        try: print "Grouping under:", art
        except UnicodeEncodeError: print "Grouping under: ???"
        #Create new artist record for adjusted artist name.
        cur.execute("SELECT artist_id FROM artists WHERE name=?",(art,))
        artists_id = cur.fetchone()
        if art[:4] == u"The ":
            art = art[4:] + u", The"
        art = art[0].upper() + art[1:]
        if artists_id == None:
            cur.execute("INSERT INTO artists (name) VALUES (?)", 
                        (art,))
            artists_id = [cur.lastrowid]
        else:
            cur.execute("UPDATE artists SET name=? WHERE artist_id=?", 
                        (art,artists_id[0]))
            
        artists_id = artists_id[0]
            
        if len(sim) > 0:            
            for x in sim:
                cur.execute("SELECT * FROM artists WHERE name=?",(x,))
                simid = cur.fetchone()
                if not simid == None:
                    simid = simid[0]
                    #Replace all occurances of the similar names with the new name.
                    cur.execute("UPDATE songs SET artist_id=? WHERE artist_id=?",
                                (artists_id,simid))
                    #Remove all the similar records.
                    if artists_id <> simid:
                        cur.execute("DELETE FROM artists WHERE artist_id=?",(simid,))
                #Remove the similar name from working sets.
                if x in all: all.remove(x)
                if x in alist: alist.remove(x)
                
    con.commit()
    

def createRDFsongs(dbDir, version, con, cur):
    print "Creating songs rdf file..."
    select = """SELECT songs.song_id, songs.name, songs.title, 
                artists.name, albums.name, songs.time, songs.track FROM songs 
                INNER JOIN artists ON songs.artist_id=artists.artist_id 
                INNER JOIN albums ON songs.album_id=albums.album_id"""
    rdf = [RDF_header]
    for r in cur.execute(select):
        rdf.append(u'<RDF:Description about="http://webamp2/song/%d" s:ID="%d">' % (r[0], r[0]))
        rdf.append(u' <s:Name>%s</s:Name>' % esc(r[1]))
        rdf.append(u' <s:Title>%s</s:Title>' % esc(r[2]))
        rdf.append(u' <s:Artist>%s</s:Artist>' % esc(r[3]))
        rdf.append(u' <s:Album>%s</s:Album>' % esc(r[4]))
        rdf.append(u' <s:Time>%s</s:Time>' % r[5])
        rdf.append(u' <s:Track nc:parseType="Integer">%s</s:Track>' % r[6])
        rdf.append(u' <s:type>file</s:type>')
        rdf.append(u'</RDF:Description>')
    rdf.append(u'</RDF:RDF>')
    rdf = u'\n'.join(rdf)
    
    old_files = glob.glob(dbDir+"/songs*.xml")
    for old_file in old_files:
        os.remove(old_file)
    
    f = codecs.open(dbDir+"/songs-%s.xml" % version, "wt",'utf-8')
    f.write(rdf)
    f.close()

def getDirChildren(id,level=0):
    con, cur = getCursor()
    cur.execute("SELECT * FROM dirs WHERE path=?", (id,))
    for d in cur:
        yield d, level
        for c, l in getDirChildren(d[0], level=level+1):
            yield c, l
            
    
def createRDFdirs(dbDir, version, con, cur):
    print "Creating directories rdf file..."
    cur.execute("SELECT dir_id, name FROM dirs ORDER BY name ASC")
    dirsRDF = [RDF_header]
    dirsRDF_Seq = [u'<RDF:Seq about="http://webamp2/dirs">']
    for d in cur:
        about = "http://webamp2/dir/%d" % d[0]
        name = esc(d[1].split("/")[-1])
        dirsRDF.append(u' <RDF:Description about="%s" s:ID="%d">' % (about, d[0]))
        dirsRDF.append(u'  <s:Name>%s</s:Name>' % name)
        dirsRDF.append(u'  <s:Title>%s</s:Title>' % name)
        dirsRDF.append(u'  <s:type>dir</s:type>')
        dirsRDF.append(u' </RDF:Description>')
        
    lst = []
    for d, l in getDirChildren(0):
        lst.append((d[0],l))
    lst.append((-1,0))
    for i in range(0, len(lst)-1):
        pad = u"  "*(lst[i][1]+1)
        if lst[i+1][1] > lst[i][1]:
            dirsRDF_Seq.append(pad + u'<RDF:li><RDF:Seq about="http://webamp2/dir/%d">' % lst[i][0])
        elif lst[i+1][1] < lst[i][1]:
            dirsRDF_Seq.append(pad + u'<RDF:li resource="http://webamp2/dir/%d"/>' % lst[i][0])
            for fallback in range(lst[i+1][1], lst[i][1]):
                dirsRDF_Seq.append((u"  "*((fallback-lst[i][1])+1)) + u'</RDF:Seq></RDF:li>')
        else:
            dirsRDF_Seq.append(pad + u'<RDF:li resource="http://webamp2/dir/%d"/>' % lst[i][0])
            
        
    dirsRDF_Seq.append(u'</RDF:Seq>')
    rdf = u'\n'.join(dirsRDF+['','']+dirsRDF_Seq+[u'</RDF:RDF>'])
    
    old_files = glob.glob(dbDir+"/dirs*.xml")
    old_files += glob.glob(dbDir+"/dirs/*.xml")
    for old_file in old_files:
        os.remove(old_file)
    
    f = codecs.open(dbDir+"/dirs-%s.xml" % version, "wt",'utf-8')
    f.write(rdf)
    f.close()


def createRDFartists(dbDir, version, con, cur):
    print "Creating artists / albums rdf file..."
    cur.execute("""SELECT artists.artist_id, artists.name, COUNT(artists.artist_id) FROM songs
                   INNER JOIN artists ON songs.artist_id=artists.artist_id
                   WHERE artists.name > '' 
                   GROUP BY artists.name
                   ORDER BY artists.name""")
    root = cur.fetchall()
    counts = [x[2] for x in root]
    counts.sort()
    cutoff = counts[-100]
    rdf = [RDF_header]
    rdfSeq = [u'<RDF:Seq about="http://webamp2/artists">']
    rdfSeqTop = [u'<RDF:Seq about="http://webamp2/top_artists">']
    
    aboutG = "http://webamp2/artist"
    aboutA = "http://webamp2/album"
    
    select = """SELECT songs.artist_id, songs.album_id FROM songs
                INNER JOIN albums ON albums.album_id=songs.album_id
                WHERE albums.name > ''
                ORDER BY songs.artist_id"""
    cur.execute(select)
    allalbums = cur.fetchall()
    for a_group in root:
        id = a_group[0]
        name = esc(a_group[1])
        albums = set([x[1] for x in allalbums if x[0] == id])
        
        rdf.append(u' <RDF:Description about="%s/%d" s:ID="%d">' % (aboutG, id, id))
        rdf.append(u'  <s:Name>%s</s:Name>' % name)
        rdf.append(u'  <s:Title>%s</s:Title>' % name)
        rdf.append(u'  <s:type>artist</s:type>')
        rdf.append(u' </RDF:Description>')
        rdfSeq.append(u' <RDF:li><RDF:Seq about="%s/%d">' % (aboutG, id))
        for album in albums:
            aid = album
            rdfSeq.append(u'  <RDF:li resource="%s/%d"/>' % (aboutA, aid))
        rdfSeq.append(u' </RDF:Seq></RDF:li>')
        
        if a_group[2] >= cutoff:
            rdfSeqTop.append(u' <RDF:li resource="%s/%d"/>' % (aboutG, id))
        
    rdfSeq.append(u'</RDF:Seq>')
    rdfSeqTop.append(u'</RDF:Seq>')
    rdf = u'\n'.join(rdf+["",""]+rdfSeq+rdfSeqTop+[u'</RDF:RDF>'])
    
    old_files = glob.glob(dbDir+"/artists*.xml")
    old_files += glob.glob(dbDir+"/artists/*.xml")
    for old_file in old_files:
        os.remove(old_file)
    
    f = codecs.open(dbDir+"/artists-%s.xml" % version, "wt",'utf-8')
    f.write(rdf)
    f.close()
    

def createRDFalbums(dbDir, version, con, cur):
    print "Creating albums rdf file..."
    cur.execute("""SELECT albums.album_id, albums.name, COUNT(albums.album_id) FROM songs
                   INNER JOIN albums ON songs.album_id=albums.album_id
                   WHERE albums.name > '' 
                   GROUP BY albums.name
                   ORDER BY albums.name""")
    albums = cur.fetchall()
    rdf = [RDF_header]
    rdfSeq = [u'<RDF:Seq about="http://webamp2/albums">']
    about = "http://webamp2/album"

    for album in albums:
        aid = album[0]
        aname = esc(album[1])
        rdf.append(u' <RDF:Description about="%s/%d" s:ID="%d">' % (about, aid, aid))
        rdf.append(u'  <s:Name>%s</s:Name>' % aname)
        rdf.append(u'  <s:Title>%s</s:Title>' % aname)
        rdf.append(u'  <s:type>album</s:type>')
        rdf.append(u' </RDF:Description>')
        if album[2] > 1:
            rdfSeq.append(u'  <RDF:li resource="%s/%d"/>' % (about, aid))
        
    rdfSeq.append(u'</RDF:Seq>')
    rdf = u'\n'.join(rdf+["",""]+rdfSeq+[u'</RDF:RDF>'])
    
    old_files = glob.glob(dbDir+"/albums*.xml")
    old_files += glob.glob(dbDir+"/albums/*.xml")
    for old_file in old_files:
        os.remove(old_file)
    
    f = codecs.open(dbDir+"/albums-%s.xml" % version, "wt",'utf-8')
    f.write(rdf)
    f.close()


def createRDF_dir(dir, fname):
    dir = int(dir)
    conn, cursor = getCursor()
    rdf = [RDF_header]
    rdf.append(u'<RDF:Seq about="http://webamp2/dir/%d/contents">' % dir)
    cursor.execute("SELECT dir_id FROM dirs WHERE dirs.path=?", (dir,))
    for d in cursor:
        rdf.append(u' <RDF:li resource="http://webamp2/dir/%d" />' % d[0])
    cursor.execute("SELECT song_id FROM songs WHERE songs.dir_id=?", (dir,))
    for d in cursor:
        rdf.append(u' <RDF:li resource="http://webamp2/song/%d" />' % d[0])
    rdf.append(u'</RDF:Seq></RDF:RDF>')
    conn.close()
    
    pth = u"/".join(fname.split("/")[0:-1])
    if not os.path.exists(pth): os.makedirs(pth)
    f = codecs.open(fname, "wt",'utf-8')
    f.write(u'\n'.join(rdf))
    f.close()
    

def createRDF_album(id, fname):
    id = int(id)
    conn, cursor = getCursor()
    rdf = [RDF_header]
    rdf.append(u'<RDF:Seq about="http://webamp2/album/%d/contents">' % id)
    if id == 0:
        rdf.append(u' <RDF:li resource="http://webamp2/albums" />')
        cursor.execute("SELECT album_id FROM albums ORDER BY name")
        for d in cursor:
            rdf.append(u' <RDF:li resource="http://webamp2/album/%d" />' % d[0])
    else:
        cursor.execute("SELECT song_id FROM songs WHERE album_id=?", (id,))
        for d in cursor:
            rdf.append(u' <RDF:li resource="http://webamp2/song/%d" />' % d[0])
    rdf.append(u'</RDF:Seq></RDF:RDF>')
    conn.close()

    pth = u"/".join(fname.split("/")[0:-1])
    if not os.path.exists(pth): os.makedirs(pth)
    f = codecs.open(fname, "wt",'utf-8')
    f.write(u'\n'.join(rdf))
    f.close()


def createRDF_artist(id, fname):
    id = int(id)
    conn, cursor = getCursor()
    rdf = [RDF_header]
    rdf.append(u'<RDF:Seq about="http://webamp2/artist/%d/contents">' % id)
    
    if (id == 0):
        cursor.execute("""SELECT artists.artist_id, artists.name, COUNT(artists.artist_id) FROM songs
                   INNER JOIN artists ON songs.artist_id=artists.artist_id
                   WHERE artists.name > '' 
                   GROUP BY artists.name
                   ORDER BY artists.name""")
        root = [x for x in cursor if x[2] > 1]
        for a in root:
            rdf.append(u'<RDF:li resource="http://webamp2/artist/%d"/>' % a[0])
    else:
        #albums = albumsByGroup(id, cursor)
        #for a in albums:
            #rdf.append(u'<RDF:li resource="http://webamp2/album/%d"/>' % a[0])
        cursor.execute("SELECT name FROM artists WHERE artist_id=?", (id,))
        name = "%"+cursor.fetchone()[0]+"%"
        select = """SELECT songs.song_id FROM songs
                    INNER JOIN artists ON songs.artist_id=artists.artist_id
                    WHERE artists.name LIKE ?"""
        cursor.execute(select, (name,)) 
        for song in cursor:
            rdf.append(u' <RDF:li resource="http://webamp2/song/%d" />' % song[0])
    
    rdf.append(u'</RDF:Seq></RDF:RDF>')
    conn.close()
    pth = u"/".join(fname.split("/")[0:-1])
    if not os.path.exists(pth): os.makedirs(pth)
    f = codecs.open(fname, "wt",'utf-8')
    f.write(u'\n'.join(rdf))
    f.close()


def createRDF_playlist(id, lst, fname):
    id = int(id)
    rdf = [RDF_header]
    rdf.append(u'<RDF:Seq about="http://webamp2/playlist/%d/contents">' % id)
    if (id == 0):
        i = 0
        for pl in lst:
            if pl <> "[deleted]":
                rdf.append(u'<RDF:li resource="http://webamp2/playlist/%d"/>' % i)
            i += 1
    else:
        for song in lst:
            rdf.append(u' <RDF:li resource="http://webamp2/song/%d" />' % song)
    
    rdf.append(u'</RDF:Seq></RDF:RDF>')
    pth = u"/".join(fname.split("/")[0:-1])
    if not os.path.exists(pth): os.makedirs(pth)
    f = codecs.open(fname, "wt",'utf-8')
    f.write(u'\n'.join(rdf))
    f.close()
    
    
def getSong(id):
    id = int(id)
    conn, cursor = getCursor()
    select = """SELECT songs.file, songs.title, albums.name, artists.name FROM songs 
                INNER JOIN albums ON songs.album_id=albums.album_id
                INNER JOIN artists on songs.artist_id=artists.artist_id
                WHERE song_id=?"""
    cursor.execute(select, (id,)) 
    song = cursor.fetchone()
    conn.close()
    return dict(file=song[0], title=song[1], album=song[2], artist=song[3])
    
def getSongsBy(artist):
    id = int(artist)
    conn, cursor = getCursor()
    cursor.execute("SELECT name FROM artists WHERE artist_id=?", (id,))
    name = "%"+cursor.fetchone()[0]+"%"
    print name
    select = """SELECT songs.file FROM songs
                INNER JOIN artists ON songs.artist_id=artists.artist_id
                WHERE artists.name LIKE ?"""
    cursor.execute(select, (name,)) 
    lst =  [x[0] for x in cursor]
    print lst
    conn.close()
    return lst


def getSongsOn(album):
    id = int(album)
    conn, cursor = getCursor()
    cursor.execute("SELECT file FROM songs WHERE album_id=?", (id,)) 
    lst =  [x[0] for x in cursor]
    conn.close()
    return lst

def search(what, where):
    conn, cursor = getCursor()
    select = """SELECT songs.song_id FROM songs 
                INNER JOIN albums ON songs.album_id=albums.album_id
                INNER JOIN artists on songs.artist_id=artists.artist_id
                """
    what = "%"+what+"%"
    values = (what,)
    if where == 'file':
        sql_where = """WHERE songs.file LIKE ?"""
    elif where == 'title':
        sql_where = """WHERE songs.title LIKE ?"""
    elif where == 'artist':
        sql_where = """WHERE artists.name LIKE ?"""
    elif where == 'album':
        sql_where = """WHERE albums.name LIKE ?"""
    else: 
        sql_where = """WHERE songs.file LIKE ? OR songs.title LIKE ? 
		    OR artists.name LIKE ? OR albums.name LIKE ?"""
        values = (what, what, what, what)
    cursor.execute(select+sql_where, values)
    return [str(x[0]) for x in cursor]
    
def getStats():
    con, cur = getCursor()
    try:
        cur.execute("SELECT * FROM stats")
        row = cur.fetchone()
    except dbapi2.OperationalError:
        row = None
    if row == None: stats = dict(artists='', albums='', songs='',
                db_playtime='', db_update='')
    else: stats = dict(artists=row[0], albums=row[1], songs=row[2],
                       db_playtime=row[3], db_update=row[4])
    con.close()
    return stats

    
def update(songs, dbDir, stats):
    con, cur = getCursor()
    doGroup = False
    for song in songs:
        dbChanged = addSong(song, con, cur, commit=False)
        if dbChanged: doGroup = True
    con.commit()
    
    dbVer = stats["db_update"]
    if not os.path.exists(dbDir+"/artists-%s.xml" %dbVer): 
        if doGroup: group(con, cur)
        createRDFartists(dbDir, dbVer, con, cur)
    if not os.path.exists(dbDir+"/albums-%s.xml" %dbVer): 
        createRDFalbums(dbDir, dbVer, con, cur)
    if not os.path.exists(dbDir+"/songs-%s.xml" %dbVer):
        createRDFsongs(dbDir, dbVer, con, cur)
    if not os.path.exists(dbDir+"/dirs-%s.xml" %dbVer): 
        createRDFdirs(dbDir, dbVer, con, cur)
    
    values = (stats['artists'], stats['albums'], stats['songs'],
              stats['db_playtime'], stats['db_update'])
    cur.execute("DELETE FROM stats WHERE 1=1")
    cur.execute("INSERT INTO stats VALUES (?,?,?,?,?)", values)
    con.commit()
    
    cur.execute("SELECT file FROM songs ORDER BY song_id ASC")
    s = [u'']+[x[0] for x in cur] # Offset list, db ids start at 1
    cur.execute("SELECT name FROM dirs ORDER BY dir_id ASC")
    d = [x[0] for x in cur] # No offset because a fake root record was added.
    con.close()
    return s, d


def markPlayed(id):
    id = int(id)
    now = datetime.datetime.now()
    unixtime = int(time.mktime(now.timetuple()))
    sql = "UPDATE songs SET playcount=playcount+1,lastplay=? WHERE song_id=?"
    con, cur = getCursor()
    cur.execute(sql, (unixtime, id))
    con.commit()
    con.close()
    

#make URL safe for insertion into findlyrics.com search url
def _clean(item):
    s = item.find("(")
    if (s > 0):
        e = item.rfind(")")
        if (e < 0):
            e = s
        item = item[:s-1] + item[e+1:]
    item = item.replace("&","and").replace("!","").replace("?","")
    item = item.replace(".","").replace(":","").replace(";","").replace("'","")
    item = item.replace('"',"").replace(","," ").replace(" - "," ")
    item = item.replace("-"," ")
    item = urllib.quote_plus(item)
    return item


def getCover(id, fname, alt):
    id = int(id)
    select = """SELECT DISTINCT artists.name, albums.name FROM songs
                INNER JOIN albums ON songs.album_id=albums.album_id
                INNER JOIN artists ON artists.artist_id=songs.artist_id
                WHERE albums.album_id=?"""
    conn, cursor = getCursor()
    cursor.execute(select, (id,))
    res = cursor.fetchall()
    if len(res) > 2:
        res = ("Various", res[0][1])
    conn.close()
    
    img = None
    for item in res:
        data = ""
        asin = ""
        artist = _clean(item[0])
        album = _clean(item[1])
        print "Searching for album cover for",album,"by",artist
        search_url = "http://musicbrainz.org/ws/1/release/?type=xml&artist="+artist+"&title="+album+"&limit=1"
        try:
            conn = urllib.urlopen(search_url)
            data = conn.read()
            conn.close()
        except IOError:
            print "IOError!"
        if (data != ""):
            s = data.find("<asin>") + 6
            if (s > 6):
                e = data.find("</asin>", s)
                if (e > 0): asin = data[s:e]
        else:
            print "NO DATA FOUND."
        if (len(asin) == 10):
            print asin
            base = "http://images.amazon.com/images/P/"+asin
            imageurl = base+".01._AA240_SCLZZZZZZZ_.jpg"
            pth = u"/".join(fname.split("/")[0:-1])
            if not os.path.exists(pth): os.makedirs(pth)
            conn = urllib.urlopen(imageurl)
            img = conn.read()
            if len(img) < 1000:
                imageurl = base+".01.MZZZZZZZ.jpg"
                conn = urllib.urlopen(imageurl)
                img = conn.read()
            conn.close()
            if len(img) > 1000: break
            else: img = None
    
    pth = u"/".join(fname.split("/")[0:-1])
    if not os.path.exists(pth): os.makedirs(pth)
    if img == None:
        f = open(alt, mode="rb")
        img = f.read()
        f.close()
    if img != None:    
        f = open(fname, mode="wb")
        f.write(img)
        f.close()
        
                
def createRDFplaylists(lst, fname):
    rdf = [RDF_header]
    rdfSeq = [u'<RDF:Seq about="http://webamp2/playlists">']
    i = 0
    for pl in lst:
        if pl <> "[deleted]":
            rdf.append(u'<RDF:Description about="http://webamp2/playlist/%d" s:ID="%d">' % (i, i))
            rdf.append(u'  <s:Name>%s</s:Name>' % esc(pl))
            rdf.append(u'  <s:Title>%s</s:Title>' % esc(pl))
            rdf.append(u'  <s:type>playlist</s:type>')
            rdf.append(u'</RDF:Description>')
            rdfSeq.append(u'<RDF:li resource="http://webamp2/playlist/%d"/>' % i)
        i += 1
    rdfSeq.append(u'</RDF:Seq></RDF:RDF>')
    print fname
    f = codecs.open(fname, "wt",'utf-8')
    f.write(u'\n'.join(rdf + rdfSeq))
    f.close()
        
        
